package com.iwhalecloud.dict.dao;

import com.iwhalecloud.dict.bean.ExcelInfoBeResult;
import com.iwhalecloud.dict.bean.ExcelInfor;
import com.iwhalecloud.dict.bean.TempAmount;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PostgreSqlDao {
    public Connection getConnection(){
        try {
            Class.forName("org.postgresql.Driver").newInstance();
            Connection connection = DriverManager.getConnection("jdbc:postgresql://172.18.0.60:5055/luoyw_db", "luoyw", "luoyw123");
            return connection;
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void insertBeDetail(List<ExcelInfor> list,Long oppId,int operType){

        cleanData("mid_opp_benefit_evaluation",oppId,operType);

        String sql="insert into mid_opp_benefit_evaluation values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection connection=getConnection();

        try {
            for (ExcelInfor excelInfor : list) {
                PreparedStatement pStatement = connection.prepareStatement(sql);
                pStatement.setString(1, excelInfor.getIndexType());
                pStatement.setString(2, excelInfor.getIndex());
                pStatement.setString(3, excelInfor.getTaxRate());
                pStatement.setDouble(4, excelInfor.getAmount());
                pStatement.setDouble(5, excelInfor.getYear1Amount());
                pStatement.setDouble(6, excelInfor.getYear2Amount());
                pStatement.setDouble(7, excelInfor.getYear3Amount());
                pStatement.setDouble(8, excelInfor.getYear4Amount());
                pStatement.setDouble(9, excelInfor.getYear5Amount());
                pStatement.setDouble(10, excelInfor.getYear6Amount());
                pStatement.setDouble(11, excelInfor.getYear7Amount());
                pStatement.setDouble(12, excelInfor.getYear8Amount());
                pStatement.setDouble(13, excelInfor.getYear9Amount());
                pStatement.setDouble(14, excelInfor.getYear10Amount());
                pStatement.setDouble(15, excelInfor.getYear11Amount());
                pStatement.setDouble(16, excelInfor.getPrice());
                pStatement.setDouble(17, excelInfor.getYear1Price());
                pStatement.setDouble(18, excelInfor.getYear2Price());
                pStatement.setDouble(19, excelInfor.getYear3Price());
                pStatement.setDouble(20, excelInfor.getYear4Price());
                pStatement.setDouble(21, excelInfor.getYear5Price());
                pStatement.setDouble(22, excelInfor.getYear6Price());
                pStatement.setDouble(23, excelInfor.getYear7Price());
                pStatement.setDouble(24, excelInfor.getYear8Price());
                pStatement.setDouble(25, excelInfor.getYear9Price());
                pStatement.setDouble(26, excelInfor.getYear10Price());
                pStatement.setDouble(27, excelInfor.getYear11Price());
                pStatement.setString(28, excelInfor.getComments());
                pStatement.setInt(29, excelInfor.getDatatype());
                pStatement.setInt(30, excelInfor.getIndexId());
                pStatement.setLong(31,oppId);
                pStatement.setInt(32,1);
                pStatement.setInt(33,operType);
                pStatement.executeUpdate();
                pStatement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public void insertBeResult(List<ExcelInfoBeResult> list,long oppId,int operType){

        cleanData("mid_opp_be_result",oppId,operType);

        String sql="insert into mid_opp_be_result values(?,?,?,?,?,?,?,?,?,?)";
        Connection connection=getConnection();
        try {
            for(ExcelInfoBeResult excelInfor:list){
                PreparedStatement pStatement = connection.prepareStatement(sql);
                pStatement.setString(1,excelInfor.getIndexType());
                pStatement.setString(2,excelInfor.getIndex());
                pStatement.setString(3,excelInfor.getIndexValue1());
                pStatement.setString(4,excelInfor.getIndexValue2());
                pStatement.setString(5,excelInfor.getComments());
                pStatement.setInt(6,excelInfor.getDataType());
                pStatement.setInt(7,excelInfor.getIndexId());
                pStatement.setLong(8,oppId);
                pStatement.setInt(9,1);
                pStatement.setInt(10,operType);
                pStatement.executeUpdate();
                pStatement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public List<TempAmount> queryDataFromDB(String sql){
        Connection connection=getConnection();
        try {
            PreparedStatement pStatement = connection.prepareStatement(sql);

            List<TempAmount> list = new ArrayList<TempAmount>();
            ResultSet rs=pStatement.executeQuery();
            while (rs.next()){
                TempAmount temp = new TempAmount();
                temp.setYear1Amount(rs.getDouble(1));
                temp.setYear2Amount(rs.getDouble(2));
                temp.setYear3Amount(rs.getDouble(3));
                temp.setYear4Amount(rs.getDouble(4));
                temp.setYear5Amount(rs.getDouble(5));
                temp.setYear6Amount(rs.getDouble(6));
                temp.setYear7Amount(rs.getDouble(7));
                temp.setYear8Amount(rs.getDouble(8));
                temp.setYear9Amount(rs.getDouble(9));
                temp.setYear10Amount(rs.getDouble(10));
                temp.setOppId(rs.getLong(11));
                temp.setIndexId(rs.getInt(12));
                list.add(temp);
            }
            rs.close();
            pStatement.close();
            connection.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    public void cleanData(String tableName,Long oppId,int operType){
        String sql="delete from  $tablename where opp_id=? and oper_type=? ";
        String sql2 = sql.replace("$tablename",tableName);

        Connection connection=getConnection();
        try{
                PreparedStatement pStatement = connection.prepareStatement(sql2);
                pStatement.setLong(1,oppId);
                pStatement.setInt(2,operType);
                pStatement.executeUpdate();
                pStatement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public int callProcedure(String functionName,Long oppId,int operType,int acct_month){
        Connection connection=getConnection();
        String procedure = "{ call $functionName (?, ?, ?, ? )}";
        String sql = procedure.replace("$functionName",functionName);

        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(sql);
            //通过 setXXX 方法将IN参数传给@id_min，其中第一个1是参数的索引值，第二个1是传给@id_min的值
            statement.setLong(1, oppId);
            statement.setInt(2, operType);
            statement.setInt(3, acct_month);
            //registerOutParameter注册输出参数@num_t123，输出参数的索引值是2
            //参数的索引值是根据占位符?出现的次序从左到右由1开始计，不管其是输入还是输出参数
            statement.registerOutParameter(4,Types.INTEGER);
            statement.execute();
            //打印输出参数@num_t123的值
            return statement.getInt(4);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

}
